package com.water.dao.impl;

import com.water.dao.WaterDao;
import com.water.domain.ArticleListBean;
import com.water.domain.ColumnListBean;
import com.water.domain.UserBean;
import com.water.domain.UserListBean;
import com.water.utils.JDBCUtil02;
import com.water.utils.TextUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class WaterDaoImpl implements WaterDao {
    /**
     * 查询总条数
     *
     * @return
     * @throws SQLException
     */
    @Override
    public int findUserlistCount() throws SQLException {
        QueryRunner runner = new QueryRunner(JDBCUtil02.getDataSource());
        //用于处理 平均值 、 总的个数。
        Long result = (Long) runner.query("SELECT COUNT(*) FROM userlist", new ScalarHandler());
        return result.intValue();
    }

    /**
     * 总栏目记录数
     *
     * @return
     * @throws SQLException
     */
    @Override
    public int findColumnCount() throws SQLException {
        QueryRunner runner = new QueryRunner(JDBCUtil02.getDataSource());
        Long result = (Long) runner.query("SELECT COUNT(*) FROM `column`", new ScalarHandler());
        return result.intValue();
    }

    /**
     * 总文章记录数
     *
     * @return
     * @throws SQLException
     */
    @Override
    public int findArticleCount() throws SQLException {
        QueryRunner runner = new QueryRunner(JDBCUtil02.getDataSource());
        Long result = (Long) runner.query("SELECT COUNT(*) FROM article", new ScalarHandler());
        return result.intValue();
    }

    /**
     * 用户分页
     *
     * @param currentPage
     * @return
     * @throws SQLException
     */
    @Override
    public List<UserListBean> findUserByPage(int currentPage) throws SQLException {
        QueryRunner runner = new QueryRunner(JDBCUtil02.getDataSource());
        String sql = "select * from userlist limit ? offset ?";
        return runner.query(sql, new BeanListHandler<UserListBean>(UserListBean.class), PAGE_SIZE, PAGE_SIZE * (currentPage - 1));

    }

    /**
     * 文章分页
     *
     * @param currentPage
     * @return
     * @throws SQLException
     */
    @Override
    public List<ArticleListBean> findArticleByPage(int currentPage) throws SQLException {
        QueryRunner runner = new QueryRunner(JDBCUtil02.getDataSource());
        String sql = "select * from article limit ? offset ?";
        return runner.query(sql, new BeanListHandler<ArticleListBean>(ArticleListBean.class), PAGE_SIZE, PAGE_SIZE * (currentPage - 1));
    }

    /**
     * 栏目分页
     *
     * @param currentPage
     * @return
     * @throws SQLException
     */
    @Override
    public List<ColumnListBean> findColumnByPage(int currentPage) throws SQLException {
        QueryRunner runner = new QueryRunner(JDBCUtil02.getDataSource());
        String sql = "select * from `column` limit ? offset ?";
        return runner.query(sql, new BeanListHandler<ColumnListBean>(ColumnListBean.class), PAGE_SIZE, PAGE_SIZE * (currentPage - 1));
    }

    /**
     * 检测用户名
     *
     * @param username
     * @return
     * @throws SQLException
     */
    @Override
    public boolean checkUserName(String username) throws SQLException {
        QueryRunner runner = new QueryRunner(JDBCUtil02.getDataSource());
        String sql = "select count(*) from users where username = ?";
        //runner.query(sql, new ScalarHandler(), username);
        Long result = (Long) runner.query(sql, new ScalarHandler(), username);
        return result > 0;

    }

    /**
     * 用户登录
     */
    @Override
    public UserBean login(UserBean user) throws SQLException {
        QueryRunner runner = new QueryRunner(JDBCUtil02.getDataSource());
        String sql = "select * from users where username = ? and password = ? ";
        UserBean query = runner.query(sql, new BeanHandler<UserBean>(UserBean.class), user.getUsername(), user.getPassword());
        return query;

    }

    /**
     * 用户注册
     *
     * @param reg
     * @throws SQLException
     */
    @Override
    public void register(UserBean reg) throws SQLException {
        QueryRunner runner = new QueryRunner(JDBCUtil02.getDataSource());
        String sql = "insert into users values(null,?,null,?)";
        runner.update(sql, reg.getUsername(), reg.getPassword());
    }

    /* *//**
     * 查询所有用户
     *//*
    @Override
    public List<UserListBean> findAll() throws SQLException {
        // TODO Auto-generated method stub
        QueryRunner runner = new QueryRunner(JDBCUtil02.getDataSource());
        String sql = "select * from userlist";
        return runner.query(sql, new BeanListHandler<UserListBean>(UserListBean.class));
    }

    *//**
     * 查询所有文章列表
     *//*
    @Override
    public List<ArticleListBean> FindArticleAll() throws SQLException {
        // TODO Auto-generated method stub
        QueryRunner runner = new QueryRunner(JDBCUtil02.getDataSource());
        String sql = "select * from article";
        return runner.query(sql, new BeanListHandler<ArticleListBean>(ArticleListBean.class));

    }

    *//**
     * 查询所有栏目
     *//*
    @Override
    public List<ColumnListBean> FindColumnAll() throws SQLException {
        // TODO Auto-generated method stub
        QueryRunner runner = new QueryRunner(JDBCUtil02.getDataSource());
        String sql = "select * from `column`";
        return runner.query(sql, new BeanListHandler<ColumnListBean>(ColumnListBean.class));
    }
*/

    /**
     * 添加栏目
     *
     * @param columnListBean
     * @throws SQLException
     */
    @Override
    public void InsertColumn(ColumnListBean columnListBean) throws SQLException {
        QueryRunner runner = new QueryRunner(JDBCUtil02.getDataSource());
        String sql = "insert into `column` (colum) values(?)";
        runner.update(sql, columnListBean.getColum());

    }

    /**
     * 模糊查询
     *
     * @param title
     * @param
     * @return
     * @throws SQLException
     */
    @Override
    public List<ArticleListBean> search(String title, String status) throws SQLException {
        QueryRunner runner = new QueryRunner(JDBCUtil02.getDataSource());
        String sql = "select * from article where 1=1 ";
        List<String> list = new ArrayList<String>();
        if (!TextUtils.isEmpty(title)) {
            sql = sql + " and title like ?";
            list.add("%" + title + "%");
        }
        if (!TextUtils.isEmpty(status)) {
            sql = sql + " and status = ?";
            list.add(status);
        }
        return runner.query(sql, new BeanListHandler<ArticleListBean>(ArticleListBean.class), list.toArray());
    }


    /**
     * 删除栏目
     *
     * @param id
     * @throws SQLException
     */
    @Override
    public void DeleteColumn(int id) throws SQLException {
        QueryRunner runner = new QueryRunner(JDBCUtil02.getDataSource());
        runner.update("delete from `column` where id=?", id);
    }

    /**
     * 删除文章
     *
     * @param id
     * @throws SQLException
     */
    @Override
    public void DeleteArticle(int id) throws SQLException {
        QueryRunner runner = new QueryRunner(JDBCUtil02.getDataSource());
        runner.update("delete from article where aid=?", id);
    }
}
